package lijiale.project.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.ResourceBundle;

import javax.swing.text.html.HTMLDocument.HTMLReader.PreAction;

public class JDBCUtil {

	private JDBCUtil() {

	}

	private static ResourceBundle rb = ResourceBundle.getBundle("lijiale.project.util.jdbc");

	private static String classname = rb.getString("driver");
	private static String user = rb.getString("user");
	private static String pass = rb.getString("pass");
	private static String url = rb.getString("url");

	static {// 加载驱动
		try {
			Class.forName(classname);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static Connection getConnection() throws SQLException {// 获得链接
		Connection con = DriverManager.getConnection(url, user, pass);
		return con;
	}

	// 关闭链接
	public static void close(Connection con, PreparedStatement ps, ResultSet rs) throws SQLException {
		if (rs != null) {
			rs.close();
			rs = null;
		}
		if (ps != null) {
			ps.close();
			ps = null;
		}
		if (con != null) {
			con.close();
			con = null;
		}
	}

	public static void close(Connection con, PreparedStatement ps) throws SQLException {
		close(con, ps, null);
	}

	public static Integer update(String sql, List<Object> vals) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		con = getConnection();
		ps = con.prepareStatement(sql);
		for (int i = 0; i < vals.size(); i++) {
			ps.setObject(i + 1, vals.get(i));
		}
		Integer i = ps.executeUpdate();
		close(con, ps);
		return i;
	}

	/**
	 * 执行DML
	 * 
	 * @param sql  sql语句
	 * @param vals 不定参数
	 * @return
	 * @throws SQLException
	 */
	public static Integer update(String sql, Object... vals) throws SQLException {
		return update(sql, Arrays.asList(vals));
		// 数组中元素类型是引用类型=》asList返回的list的元素就是数组中的每个元素
		// 数组中元素类型是基本数据类型=》asList返回的list元素仅有一个，即这个数组，把这个数组当作一个元素返回
	}

	/**
	 * 执行DQL
	 * 
	 * @param <T>    返回的集合中的元素类型
	 * @param sql    执行的sql
	 * @param vals   执行的sql的参数
	 * @param clazz  返回的集合中的元素类型的类的描述
	 * @param fields key：属性名， value： 字段名
	 * @return
	 * @throws SQLException
	 */
	public static <T> List<T> queryList(String sql, List<Object> vals, Class<T> clazz, Map<String, String> fields)
			throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<T> result = new ArrayList<>();
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < vals.size(); i++)
				ps.setObject(i + 1, vals.get(i));
			rs = ps.executeQuery();
			while (rs.next()) {
				T model = clazz.newInstance();
				// 映射
				for (Entry<String, String> entry : fields.entrySet()) {
					// 用反射的形式，得到定义出来的这个字段的反射对象。
					Field field = clazz.getDeclaredField(entry.getKey());
					// 消除访问限制
					field.setAccessible(true);
					// 调用set方法把对象当中的属性附上对应的值
					field.set(model, rs.getObject(entry.getValue()));
				}
				result.add(model);
			}
		} catch (SQLException | InstantiationException | IllegalAccessException | NoSuchFieldException
				| SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(conn, ps, rs);
		}
		return result;
	}

	public static <T> List<T> queryList(String sql, List<Object> vals, Class<T> clazz, List<String> props)
			throws SQLException {
		Map<String, String> fields = new HashMap<>();
		for (String p : props) {
			fields.put(p, p);
		}
		return queryList(sql, vals, clazz, fields);
	}

	public static Integer queryInt(String sql, List<Object> vals) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(sql);
			for (int i = 0; i < vals.size(); i++) {
				ps.setObject(i + 1, vals.get(i));
			}
			rs = ps.executeQuery();
			if (rs.next())
				return rs.getInt(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}
